package com.ssms.service;

import com.ssms.commons.ESQueryParam;
import com.ssms.commons.Result;
import com.ssms.commons.TablePage;
import com.ssms.dao.*;
import com.ssms.model.*;
import com.ssms.util.BigDecimalUtil;
import com.ssms.util.ExcelUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.*;

@Service
public class ExamScoreService {

    @Resource
    private ExamScoreMapper examScoreMapper;
    @Resource
    private SubjectMapper subjectMapper;
    @Resource
    private ClazzMapper clazzMapper;
    @Resource
    private CourseMapper courseMapper;
    @Resource
    private SysUserMapper sysUserMapper;
    @Resource
    private NoticeMapper noticeMapper;

    public Result save(MultipartFile file) throws IOException {
        LinkedList<Map<String, String>> data = readData(file);
        List<Integer> errors = new ArrayList<>();
        List<Integer> repeateds = new ArrayList<>();
        List<ExamScore> examScores = convert(data, errors, repeateds);
        int i = 0;
        if(examScores.size() > 0){
            i = examScoreMapper.insertBatch(examScores);
            cal();
        }
        String msg = "<p>总计：【"+data.size()+"】行；</p><p>录入：【" + i + "】行；</p>";
        if(errors.size() > 0){
            msg += "<p>数据错误行号：【"+ StringUtils.join(errors,",") +"】；</p>";
        }
        if(repeateds.size() > 0){
            msg += "<p>数据重复行号：【"+ StringUtils.join(repeateds,",") +"】；</p>";
        }

        return Result.success(msg);
    }

    // 计算所有学生平均绩点或者挂科，生成消息
    private void cal(){
        List<SysUser> admins = sysUserMapper.selectAll(1);

        // 挂科数
        List<SysUser> flunks = examScoreMapper.selectFlunks();
        if(flunks != null && flunks.size() > 0){
            // 创建通知，关联到对应学生以及辅导员，管理员
            for (SysUser stu : flunks) {
                Notice notice = new Notice();
                notice.setContent(stu.getUsername()+"请注意，挂科数已大于或等于3门！");
                notice.setPublishTime(new Date());
                sendNotice(admins,notice,stu);
            }
        }

        // 平均绩点
        List<SysUser> stus = sysUserMapper.selectAll(3);
        for (SysUser stu : stus) {
            // 获取该学生所有成绩
            List<ExamScore> examScores = examScoreMapper.selectByStuId(stu.getId());
            if(examScores != null && examScores.size() > 0){
                String credit = calCredit(examScores);
                if(Double.valueOf(credit) < 2.4){
                    Notice notice = new Notice();
                    notice.setContent(stu.getUsername()+"请注意，平均绩点已经低于2.4！");
                    notice.setPublishTime(new Date());
                    sendNotice(admins,notice,stu);
                }
            }
        }
    }

    /** 计算平均绩点
     * 60分=1绩点，每增1分绩点增加0.1，最大5，
     * 加权平均绩点=∑(绩点*学分) / ∑(学分)
     * @param examScores
     * @return
     */
    private String calCredit(List<ExamScore> examScores){
        String tot1 = "0";
        String tot2 = "0";

        for (ExamScore examScore : examScores) {
            String credit = "1";
            int score = examScore.getScore();
            if(score < 60){
                credit = "0";
            }else{
                String res = BigDecimalUtil.div((score - 60) + "", "10");
                credit = BigDecimalUtil.add(credit,res);
            }

            tot1 = BigDecimalUtil.add(tot1, BigDecimalUtil.mul(credit, examScore.getCourse().getCredit()+"")); // ∑(绩点*学分)
            tot2 = BigDecimalUtil.add(tot2,examScore.getCourse().getCredit()+""); // ∑(学分)
        }
        return BigDecimalUtil.div(tot1,tot2);
    }


    private void sendNotice(List<SysUser> admins,Notice notice,SysUser stu){
        for (SysUser admin : admins) {
            notice.setForUserId(admin.getId()); // 通知到管理员
            noticeMapper.insertSelective(notice);
        }

        SysUser instructor = sysUserMapper.selectInstructor(stu.getClazzId());
        notice.setForUserId(instructor.getId());// 通知到学生班级的辅导员
        noticeMapper.insertSelective(notice);

        notice.setForUserId(stu.getId()); // 通知到改学生
        noticeMapper.insertSelective(notice);
    }


    // 转换数据并做数据校验
    private List<ExamScore> convert(LinkedList<Map<String,String>> list, List<Integer> errors, List<Integer> repeateds){
        List<ExamScore> scores = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Map<String, String> item = list.get(i);
            Subject subject = subjectMapper.selectByNo(item.get("subjectNo"));
            if(subject == null){
                errors.add(i+2); // 专业编号错误 ，加2便于对应excel文件中行号
                continue;
            }

            Course course = courseMapper.selectByName(item.get("courseName"));
            if(course == null){
                errors.add(i+2); // 课程名称错误
                continue;
            }else if(course.getSbjId() != subject.getId()){ // 课程与专业不符
                errors.add(i+2);
                continue;
            }

            Clazz clazz = clazzMapper.selectByClzNoAndSbjNo(item.get("clazzNo"), item.get("subjectNo"));
            if(clazz == null){
                errors.add(i+2); // 班级编号错误
                continue;
            }

            String score = item.get("score");
            System.out.println(StringUtils.isEmpty(score));
            System.out.println(score.startsWith("-"));
            System.out.println(score.matches("([1-9]\\d*\\.?\\d*)|(0\\.\\d*[1-9])"));
            if(StringUtils.isEmpty(score) || score.startsWith("-") || !score.matches("([1-9]\\d*\\.?\\d*)|(0\\.\\d*[1-9])")){
                errors.add(i+2); // 分数格式错误
                continue;
            }

            if(Integer.valueOf(score) > course.getScore()){
                errors.add(i+2); // 分数大于科目设定总分
                continue;
            }

            // 学号校验,要关联到班级，避免学号对的，但不属于对应班级
            SysUser student = sysUserMapper.selectByCode(item.get("stuNo"),clazz.getId());
            if(student == null){
                errors.add(i+2); // 学号错误
                continue;
            }


            ExamScore es = new ExamScore();
            es.setClazzId(clazz.getId());
            es.setCourseId(course.getId());
            es.setSubjectId(subject.getId());
            es.setStuId(student.getId());
            es.setScore(Integer.valueOf(score));

            // 校验是否重复录入
            int n = examScoreMapper.checkConflict(es);
            if(n > 0){
                repeateds.add(i+1);
                continue;
            }

            scores.add(es);
        }

        return scores;
    }


    // 读取excel数据
    public LinkedList<Map<String,String>> readData(MultipartFile file) throws IOException {
        LinkedList<Map<String,String>> list = new LinkedList<>();

        Workbook wb0 = new XSSFWorkbook(file.getInputStream());
        //获取Excel文档中的第一个表单
        Sheet sht0 = wb0.getSheetAt(0);
        //对Sheet中的每一行进行迭代
        for (Row r : sht0) {
            if(r.getRowNum() < 1){
                continue;
            }

            // 逐个取出一行的每个单元格数据
            Map<String,String> item = new HashMap<>();
            item.put("stuNo",r.getCell(0).getStringCellValue());
            item.put("clazzNo",r.getCell(1).getStringCellValue());
            item.put("courseName",r.getCell(2).getStringCellValue());
            item.put("subjectNo",r.getCell(3).getStringCellValue());
            item.put("score",r.getCell(4).getStringCellValue());
            list.add(item);
        }
        return list;
    }


    public TablePage list(TablePage tablePage, ESQueryParam esQueryParam) {
        int size = tablePage.getLimit();
        int start = (tablePage.getPage() - 1) * size;
        // 获取分页数据
        List<ExamScore> roles = examScoreMapper.selectPage(start, size, esQueryParam);
        // 获取符合条件的总数
        int count = examScoreMapper.selectPageCount(esQueryParam);
        tablePage.setCount(count);
        tablePage.setData(roles);
        return tablePage;
    }


    public Result update(ExamScore examScore,Integer instor){
        if(instor != null){
            List<Integer> ownerClazzIds = getOwnerClazzIds(instor);
            if(!ownerClazzIds.contains(examScore.getClazzId())){
                return Result.failed("只能修改所带班级学生分数",500);
            }
        }

        Course course = courseMapper.selectByPrimaryKey(examScore.getCourseId());
        if(examScore.getScore() < 0){
            return Result.failed("分数不能为负数",500);
        }

        if(course.getScore() < examScore.getScore()){
            return Result.failed("科目最高分为：" + course.getScore(),500);
        }

        examScoreMapper.updateByPrimaryKeySelective(examScore);
        return Result.success();
    }

    // 查询某个学生具体分数
    public Result query(Integer stuId){
        List<ExamScore> examScores = examScoreMapper.selectByStuId(stuId);
        String avgCredit = calCredit(examScores);
        Map<String,Object> map = new HashMap<>();
        map.put("list",examScores);
        map.put("avgCredit",avgCredit);
        return Result.success(map);
    }

    // 导出某个学生成绩表
    public Result createFile(Integer stuId, HttpServletResponse response) throws UnsupportedEncodingException {
        List<ExamScore> examScores = examScoreMapper.selectByStuId(stuId);
        String avgCredit = calCredit(examScores);

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("成绩表");
        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);

        int line = 0; // 行号

        XSSFRow row = sheet.createRow(line++); // 标题行
        String[] titles = new String[]{"课程名称","课程性质","课程学分","课程成绩"};
        for (int i = 0; i < titles.length; i++) {
            row.createCell(i).setCellValue(titles[i]);
        }

        for (ExamScore examScore : examScores) {
            XSSFRow rowData = sheet.createRow(line++);
            rowData.createCell(0).setCellValue(examScore.getCourse().getCourseName());
            rowData.createCell(1).setCellValue(examScore.getCourse().getCategory());
            rowData.createCell(2).setCellValue(examScore.getCourse().getCredit());
            rowData.createCell(3).setCellValue(examScore.getScore());
        }

        XSSFRow rowData = sheet.createRow(line);
        rowData.createCell(0).setCellValue("平均绩点");

        XSSFCell cell = rowData.createCell(1);
        cell.setCellValue(avgCredit);
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        cell.setCellStyle(style);

        CellRangeAddress region = new CellRangeAddress(line,line,1,3);
        sheet.addMergedRegion(region);
        ExcelUtil.export(workbook,response,"成绩表");
        return null;
    }

    // 获取辅导员自身所带班级id
    private List<Integer> getOwnerClazzIds(Integer instor){
        List<Integer> list = new ArrayList<>();
        List<Clazz> clazzes = clazzMapper.selectByInstructor(instor);
        if(clazzes != null){
            for (Clazz clazz : clazzes) {
                list.add(clazz.getId());
            }
        }
        return list;
    }

    public Result delete(ExamScore examScore,Integer instor) {
        if(instor != null){
            List<Integer> ownerClazzIds = getOwnerClazzIds(instor);
            if(!ownerClazzIds.contains(examScore.getClazzId())){
                return Result.failed("只能删除所带班级学生考试成绩",500);
            }
        }
        examScoreMapper.deleteByPrimaryKey(examScore.getId());
        return Result.success();
    }
}
